﻿CREATE PROCEDURE [dbo].[sp_Map_Insert_Order]
	@OrderID INT,
	@AccountID VARCHAR(10),
	@PeroidYear VARCHAR(4),
	@PeroidMonth VARCHAR(2),
	@ZipIDs VARCHAR(MAX)
AS
	BEGIN TRAN T1
	
	SELECT @OrderID = OrderID FROM Orders 
	WHERE AccountID = @AccountID AND PeroidYear = @PeroidYear AND PeroidMonth = @PeroidMonth

	IF @OrderID = 0
	BEGIN
		-- Generate new order
		INSERT INTO Orders (AccountID, PeroidYear, PeroidMonth, IsPurchased)
		VALUES (@AccountID, @PeroidYear, @PeroidMonth, 0);
		
		SET @OrderID=@@IDENTITY;
		
	END
		
	IF (@ZipIDs <> '')
	BEGIN
			IF CHARINDEX(',', @ZipIDs) = 0
			BEGIN
				DELETE FROM OrderZip WHERE OrderID = @OrderID AND ZipID = @ZipIDs
				-- Insert new items for this order
				INSERT INTO OrderZip (OrderID, ZipID, Type)
				--VALUES 
				SELECT OrderID = @OrderID , @ZipIDs, 0
			END
			ELSE
			BEGIN
				DELETE FROM OrderZip WHERE OrderID = @OrderID AND
				ZipID IN (SELECT subcolstr FROM  udf_CharlistToTable(@ZipIDs, ','))
				-- Insert new items for this order
				INSERT INTO OrderZip (OrderID, ZipID, Type)
				--VALUES 
				SELECT OrderID=@OrderID, subcolstr, 0 FROM udf_CharlistToTable(@ZipIDs, ',');
			END
	END	
	
	IF ( @@ERROR <> 0 )
	BEGIN
		ROLLBACK TRAN T1
	END

	COMMIT TRAN T1
	
	RETURN
